iT邦幫忙

2021 iThome 鐵人賽

DAY 9
0
Software Development

MYSQL-相關實務操作學習紀錄系列 第 9

Day.9 備份還原 - 還原資料 (MYSQL binlog )-下

  • 分享至 

  • xImage
  •  

前情提要: 在前一天的部分我們備份好user資料庫數據,和準備資料的動作 <<

那就直接進入主題->還原步驟/images/emoticon/emoticon30.gif

  1. 開新連線(圖右),臨時關閉binlog (使binlog不會紀錄到恢復時的內容)
    Q:為何要關閉binlog ?
    A:因為在匯入備份檔時,也會紀錄binlog。而這些內容對於後續復原來說是沒作用的並且會混亂。(也可以選擇先備份起來)

https://ithelp.ithome.com.tw/upload/images/20210817/201308803RynHC1Q3F.png

mysql> SET sql_log_bin=0;

https://ithelp.ithome.com.tw/upload/images/20210817/20130880lIPCnVWTfK.png

  1. 在關閉binlog的session匯入備份檔 (恢復到備份時的紀錄)
mysql> use user

mysql> source /mnt/mysql_data/mysql/user_backup_20210817.sql
  1. 找到誤刪資料的SQL執行位置,處理後續異動資料。(這句SQL: delete from user_powers where group_id = 19200)
root@mysql-master-1:/mnt/mysql_data/mysql# mysqlbinlog -vv mysql-bin.000013 | cat -n | grep -iw 'delete'
root@mysql-master-1:/mnt/mysql_data/mysql# mysqlbinlog -vv mysql-bin.000014 | cat -n | grep -iw 'delete'
    78	### DELETE FROM `user`.`user_powers`
    84	### DELETE FROM `user`.`user_powers`
    90	### DELETE FROM `user`.`user_powers`
    96	### DELETE FROM `user`.`user_powers`
   102	### DELETE FROM `user`.`user_powers`

root@mysql-master-1:/mnt/mysql_data/mysql# mysqlbinlog -vv mysql-bin.000014 | cat -n | sed -n '55,120p';

https://ithelp.ithome.com.tw/upload/images/20210817/20130880Av6ChcKGMk.png

4.導入binlog復原後續操作紀錄

恢復範圍:

mysql-bin.000013: 還記得我們在備份時有刷新log 產生新紀錄檔mysql-bin.000013 所以內容直接全部恢復。
mysql-bin.000014: 紀錄範圍則到(# at 467)執行刪除資料前的位置(參考上圖)。

  • --start-position:備份後pos點 (多個binlog檔案->則為第一個binlog檔案POS點)
  • --stop-position: 誤操作前pos點 (多個binlog檔案->則為最後的binlog檔案POS點)

如果在備份時沒有刷新log,等於後續紀錄包含在某個binlog中。這時候要從哪邊開始恢復就是看備份時紀錄的POS位置等於要設定 ex. --start-position=154

使用注意: 在前面備份還原時我們針對單一DB做數據庫還原,不會動到其他DB數據。透過binlog還原後續資料時如果沒指定單一庫 --database=user,執行到一些其他DB的異動像已經刪除的或是插入值異動是會噴錯的!!
=binlog紀錄是包含所有DB操作內容的=

root@mysql-master-1:/mnt/mysql_data/mysql# mysqlbinlog --stop-position=467 mysql-bin.000013 mysql-bin.000014 --database=user --skip-gtids | mysql -uroot -p1234

成功回到誤刪資料前的狀態/images/emoticon/emoticon34.gif

mysql> select * from user_powers;
+----------+-----------+----------+---------------------+
| user_id  | nick_name | group_id | updated_at          |
+----------+-----------+----------+---------------------+
| 1021211  | ken       |   119201 | 2021-08-10 08:33:56 |
| 1232123  | siangx    |    19200 | 2021-08-17 02:08:05 |
| 18675543 | kitty     |    19200 | 2021-08-10 08:33:51 |
| 23122141 | kiki      |  1242000 | 2021-08-12 09:01:54 |
| 24122441 | snow      |   119201 | 2021-08-10 08:33:51 |
| 38921112 | shawn     |    19200 | 2021-08-12 10:33:51 |
| 4356231  | lisa      |    19200 | 2021-08-10 08:33:56 |
| 64121211 | kevin     |   389110 | 2021-08-10 08:33:51 |
| 72197123 | eric      |    19200 | 2021-08-15 01:33:51 |
| 7622441  | ben       |   213123 | 2021-08-10 08:33:56 |
| 91311233 | alex      |   389110 | 2021-08-10 08:33:56 |
| 91675543 | kay       |   123321 | 2021-08-10 08:33:56 |
| 98112111 | leo       |    19201 | 2021-08-13 12:33:56 |
| 98212321 | sara      |   119201 | 2021-08-10 08:33:51 |
+----------+-----------+----------+---------------------+
14 rows in set (0.00 sec)

明天來介紹一下對於日誌不斷增長好用的日誌切割管理工具~


上一篇
Day.8 備份還原 - 備份資料 (MYSQL binlog )-上
下一篇
Day10 日誌管理工具 - Logrotate (Linux)
系列文
MYSQL-相關實務操作學習紀錄30
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言